<!DOCTYPE html>
<html lang="zh-CN" data-theme="light">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width,initial-scale=1" />
    <meta name="generator" content="VuePress 2.0.0-beta.38" />
    <meta name="theme" content="VuePress Theme Hope" />
    <meta property="og:url" content="https://javaguide.cn/database/mysql/innodb-implementation-of-mvcc.html"><meta property="og:site_name" content="JavaGuide"><meta property="og:title" content="InnoDB存储引擎对MVCC的实现"><meta property="og:type" content="article"><meta property="og:updated_time" content="2021-11-09T10:47:58.000Z"><meta property="og:locale" content="zh-CN"><meta property="article:tag" content="MySQL"><meta property="article:modified_time" content="2021-11-09T10:47:58.000Z"><script>var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?5dd2e8c97962d57b7b8fea1737c01743";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();</script><link rel="stylesheet" href="//at.alicdn.com/t/font_2922463_99aa80ii7cf.css"><title>InnoDB存储引擎对MVCC的实现 | JavaGuide</title><meta name="description" content="Java学习&&面试指南">
    <style>
      :root {
        --bg-color: #fff;
      }

      html[data-theme="dark"] {
        --bg-color: #1d2025;
      }

      html,
      body {
        background-color: var(--bg-color);
      }
    </style>
    <script>
      const userMode = localStorage.getItem("vuepress-theme-hope-scheme");
      const systemDarkMode =
        window.matchMedia &&
        window.matchMedia("(prefers-color-scheme: dark)").matches;

      if (userMode === "dark" || (userMode !== "light" && systemDarkMode)) {
        document.querySelector("html").setAttribute("data-theme", "dark");
      }
    </script>
    <link rel="stylesheet" href="/assets/style.aa943f56.css">
    <link rel="modulepreload" href="/assets/app.93341f6d.js"><link rel="modulepreload" href="/assets/innodb-implementation-of-mvcc.html.c1fba416.js"><link rel="modulepreload" href="/assets/innodb-implementation-of-mvcc.html.b7310c5b.js"><link rel="modulepreload" href="/assets/plugin-vue_export-helper.21dcd24c.js">
  </head>
  <body>
    <div id="app"><!--[--><!--[--><!--[--><span tabindex="-1"></span><a href="#main-content" class="skip-link sr-only">Skip to content</a><!--]--><div class="theme-container has-toc sidebar-open"><!--[--><header class="navbar"><button class="toggle-sidebar-button" title="Toggle Sidebar"><span class="icon"></span></button><a href="/" class="home-link"><img class="logo" src="/logo.png" alt="JavaGuide"><!----><span class="site-name hide-in-pad">JavaGuide</span><!--[--><!----><!--]--></a><nav class="nav-links" style=""><div class="nav-item hide-in-mobile"><a href="/home.html" class="nav-link" arialabel="面试指南"><i class="icon iconfont icon-java"></i>面试指南<!----></a></div><div class="nav-item hide-in-mobile"><a href="/zhuanlan/" class="nav-link" arialabel="优质专栏"><i class="icon iconfont icon-recommend"></i>优质专栏<!----></a></div><div class="nav-item hide-in-mobile"><a href="/open-source-project/" class="nav-link" arialabel="项目精选"><i class="icon iconfont icon-github"></i>项目精选<!----></a></div><div class="nav-item hide-in-mobile"><a href="/books/" class="nav-link" arialabel="书籍精选"><i class="icon iconfont icon-book"></i>书籍精选<!----></a></div><div class="nav-item hide-in-mobile"><a href="https://snailclimb.gitee.io/javaguide/#/" rel="noopener noreferrer" target="_blank" arialabel="旧版链接" class="nav-link"><i class="icon iconfont icon-java"></i>旧版链接<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!----></a></div><div class="nav-item hide-in-mobile"><a href="https://javaguide.cn/feed.json" rel="noopener noreferrer" target="_blank" arialabel="RSS订阅" class="nav-link"><i class="icon iconfont icon-rss"></i>RSS订阅<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!----></a></div><div class="nav-item hide-in-mobile"><a href="/about-the-author/" class="nav-link" arialabel="关于作者"><i class="icon iconfont icon-zuozhe"></i>关于作者<!----></a></div></nav><div class="nav-actions-wrapper"><!--[--><!----><!--]--><div class="nav-item"><!----></div><div class="nav-item"><a class="repo-link" href="https://github.com/Snailclimb/JavaGuide" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" class="icon github-icon" viewbox="0 0 1024 1024" arialabelledby="github" style="width:1.25rem;height:1.25rem;vertical-align:middle;"><title id="github" lang="en">github icon</title><g fill="currentColor"><path d="M511.957 21.333C241.024 21.333 21.333 240.981 21.333 512c0 216.832 140.544 400.725 335.574 465.664 24.49 4.395 32.256-10.07 32.256-23.083 0-11.69.256-44.245 0-85.205-136.448 29.61-164.736-64.64-164.736-64.64-22.315-56.704-54.4-71.765-54.4-71.765-44.587-30.464 3.285-29.824 3.285-29.824 49.195 3.413 75.179 50.517 75.179 50.517 43.776 75.008 114.816 53.333 142.762 40.79 4.523-31.66 17.152-53.377 31.19-65.537-108.971-12.458-223.488-54.485-223.488-242.602 0-53.547 19.114-97.323 50.517-131.67-5.035-12.33-21.93-62.293 4.779-129.834 0 0 41.258-13.184 134.912 50.346a469.803 469.803 0 0 1 122.88-16.554c41.642.213 83.626 5.632 122.88 16.554 93.653-63.488 134.784-50.346 134.784-50.346 26.752 67.541 9.898 117.504 4.864 129.834 31.402 34.347 50.474 78.123 50.474 131.67 0 188.586-114.73 230.016-224.042 242.09 17.578 15.232 33.578 44.672 33.578 90.454v135.85c0 13.142 7.936 27.606 32.854 22.87C862.25 912.597 1002.667 728.747 1002.667 512c0-271.019-219.648-490.667-490.71-490.667z"></path></g></svg></a></div><div class="nav-item hide-in-mobile"><button id="appearance-switch"><svg xmlns="http://www.w3.org/2000/svg" class="icon auto-icon" viewbox="0 0 1024 1024" arialabelledby="auto" style="display:block;"><title id="auto" lang="en">auto icon</title><g fill="currentColor"><path d="M512 992C246.92 992 32 777.08 32 512S246.92 32 512 32s480 214.92 480 480-214.92 480-480 480zm0-840c-198.78 0-360 161.22-360 360 0 198.84 161.22 360 360 360s360-161.16 360-360c0-198.78-161.22-360-360-360zm0 660V212c165.72 0 300 134.34 300 300 0 165.72-134.28 300-300 300z"></path></g></svg><svg xmlns="http://www.w3.org/2000/svg" class="icon dark-icon" viewbox="0 0 1024 1024" arialabelledby="dark" style="display:none;"><title id="dark" lang="en">dark icon</title><g fill="currentColor"><path d="M524.8 938.667h-4.267a439.893 439.893 0 0 1-313.173-134.4 446.293 446.293 0 0 1-11.093-597.334A432.213 432.213 0 0 1 366.933 90.027a42.667 42.667 0 0 1 45.227 9.386 42.667 42.667 0 0 1 10.24 42.667 358.4 358.4 0 0 0 82.773 375.893 361.387 361.387 0 0 0 376.747 82.774 42.667 42.667 0 0 1 54.187 55.04 433.493 433.493 0 0 1-99.84 154.88 438.613 438.613 0 0 1-311.467 128z"></path></g></svg><svg xmlns="http://www.w3.org/2000/svg" class="icon light-icon" viewbox="0 0 1024 1024" arialabelledby="light" style="display:none;"><title id="light" lang="en">light icon</title><g fill="currentColor"><path d="M952 552h-80a40 40 0 0 1 0-80h80a40 40 0 0 1 0 80zM801.88 280.08a41 41 0 0 1-57.96-57.96l57.96-58a41.04 41.04 0 0 1 58 58l-58 57.96zM512 752a240 240 0 1 1 0-480 240 240 0 0 1 0 480zm0-560a40 40 0 0 1-40-40V72a40 40 0 0 1 80 0v80a40 40 0 0 1-40 40zm-289.88 88.08-58-57.96a41.04 41.04 0 0 1 58-58l57.96 58a41 41 0 0 1-57.96 57.96zM192 512a40 40 0 0 1-40 40H72a40 40 0 0 1 0-80h80a40 40 0 0 1 40 40zm30.12 231.92a41 41 0 0 1 57.96 57.96l-57.96 58a41.04 41.04 0 0 1-58-58l58-57.96zM512 832a40 40 0 0 1 40 40v80a40 40 0 0 1-80 0v-80a40 40 0 0 1 40-40zm289.88-88.08 58 57.96a41.04 41.04 0 0 1-58 58l-57.96-58a41 41 0 0 1 57.96-57.96z"></path></g></svg></button></div><form class="search-box" role="search"><input type="search" placeholder="搜索" autocomplete="off" spellcheck="false" value><!----></form><button class="toggle-navbar-button" aria-label="Toggle Navbar" aria-expanded="false" aria-controls="nav-screen"><span class="button-container"><span class="button-top"></span><span class="button-middle"></span><span class="button-bottom"></span></span></button><!--[--><!----><!--]--></div></header><!----><!--]--><!----><div class="toggle-sidebar-wrapper"><span class="arrow left"></span></div><aside class="sidebar"><!--[--><!----><!--]--><ul class="sidebar-links"><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-mianshi"></i><span class="title">面试准备</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-java"></i><span class="title">Java</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-computer"></i><span class="title">计算机基础</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable active"><i class="icon iconfont icon-database"></i><span class="title">数据库</span><span class="arrow down"></span></button><ul class="sidebar-links"><li><!--[--><a href="/database/basis.html" class="nav-link sidebar-link sidebar-page" arialabel="数据库基础知识"><!---->数据库基础知识<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/character-set.html" class="nav-link sidebar-link sidebar-page" arialabel="字符集"><!---->字符集<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable active"><i class="icon iconfont icon-mysql"></i><span class="title">MySQL</span><span class="arrow down"></span></button><ul class="sidebar-links"><li><!--[--><a href="/database/mysql/mysql-questions-01.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL知识点&amp;面试题总结"><!---->MySQL知识点&amp;面试题总结<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/a-thousand-lines-of-mysql-study-notes.html" class="nav-link sidebar-link sidebar-page" arialabel="一千行 MySQL 学习笔记"><!---->一千行 MySQL 学习笔记<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/mysql-high-performance-optimization-specification-recommendations.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL高性能优化规范建议"><!---->MySQL高性能优化规范建议<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable active"><i class="icon iconfont icon-important"></i><span class="title">重要知识点</span><span class="arrow down"></span></button><ul class="sidebar-links"><li><!--[--><a href="/database/mysql/mysql-index.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL索引详解"><!---->MySQL索引详解<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/mysql-logs.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL三大日志(binlog、redo log和undo log)详解"><!---->MySQL三大日志(binlog、redo log和undo log)详解<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/transaction-isolation-level.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL事务隔离级别详解"><!---->MySQL事务隔离级别详解<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html" class="router-link-active router-link-exact-active nav-link active sidebar-link sidebar-page active" arialabel="InnoDB存储引擎对MVCC的实现"><!---->InnoDB存储引擎对MVCC的实现<!----></a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#一致性非锁定读和锁定读" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="一致性非锁定读和锁定读"><!---->一致性非锁定读和锁定读<!----></a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#一致性非锁定读" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="一致性非锁定读"><!---->一致性非锁定读<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#锁定读" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="锁定读"><!---->锁定读<!----></a><ul class="sidebar-sub-headers"></ul></li></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#innodb-对-mvcc-的实现" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="InnoDB 对 MVCC 的实现"><!---->InnoDB 对 MVCC 的实现<!----></a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#隐藏字段" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="隐藏字段"><!---->隐藏字段<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#readview" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="ReadView"><!---->ReadView<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#undo-log" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="undo-log"><!---->undo-log<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#数据可见性算法" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="数据可见性算法"><!---->数据可见性算法<!----></a><ul class="sidebar-sub-headers"></ul></li></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#rc-和-rr-隔离级别下-mvcc-的差异" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="RC 和 RR 隔离级别下 MVCC 的差异"><!---->RC 和 RR 隔离级别下 MVCC 的差异<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#mvcc-解决不可重复读问题" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="MVCC 解决不可重复读问题"><!---->MVCC 解决不可重复读问题<!----></a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#在-rc-下-readview-生成情况" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="在 RC 下 ReadView 生成情况"><!---->在 RC 下 ReadView 生成情况<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#在-rr-下-readview-生成情况" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="在 RR 下 ReadView 生成情况"><!---->在 RR 下 ReadView 生成情况<!----></a><ul class="sidebar-sub-headers"></ul></li></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#mvcc➕next-key-lock-防止幻读" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="MVCC➕Next-key-Lock 防止幻读"><!---->MVCC➕Next-key-Lock 防止幻读<!----></a><ul class="sidebar-sub-headers"></ul></li><li class="sidebar-sub-header"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#参考" class="router-link-active router-link-exact-active nav-link sidebar-link heading" arialabel="参考"><!---->参考<!----></a><ul class="sidebar-sub-headers"></ul></li></ul><!--]--></li><li><!--[--><a href="/database/mysql/how-sql-executed-in-mysql.html" class="nav-link sidebar-link sidebar-page" arialabel="SQL语句在MySQL中的执行过程"><!---->SQL语句在MySQL中的执行过程<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/some-thoughts-on-database-storage-time.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL数据库时间类型数据存储建议"><!---->MySQL数据库时间类型数据存储建议<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li><li><!--[--><a href="/database/mysql/index-invalidation-caused-by-implicit-conversion.html" class="nav-link sidebar-link sidebar-page" arialabel="MySQL中的隐式转换造成的索引失效"><!---->MySQL中的隐式转换造成的索引失效<!----></a><ul class="sidebar-sub-headers"></ul><!--]--></li></ul></section><!--]--></li></ul></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-redis"></i><span class="title">Redis</span><span class="arrow right"></span></button><!----></section><!--]--></li></ul></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-Tools"></i><span class="title">开发工具</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-xitongsheji"></i><span class="title">系统设计</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-distributed-network"></i><span class="title">分布式</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-et-performance"></i><span class="title">高性能</span><span class="arrow right"></span></button><!----></section><!--]--></li><li><!--[--><section class="sidebar-group"><button class="sidebar-heading clickable"><i class="icon iconfont icon-CalendarAvailability-1"></i><span class="title">高可用</span><span class="arrow right"></span></button><!----></section><!--]--></li></ul><!--[--><!----><!--]--></aside><!--[--><main class="page" id="main-content"><!----><nav class="breadcrumb disable"></nav><div class="page-title"><h1><!---->InnoDB存储引擎对MVCC的实现</h1><div class="article-info"><span class="author-info" arialabel="作者🖊" isoriginal="false" pageview="false" color="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon author-icon" viewbox="0 0 1024 1024" arialabelledby="author"><title id="author" lang="en">author icon</title><g fill="currentColor"><path d="M649.6 633.6c86.4-48 147.2-144 147.2-249.6 0-160-128-288-288-288s-288 128-288 288c0 108.8 57.6 201.6 147.2 249.6-121.6 48-214.4 153.6-240 288-3.2 9.6 0 19.2 6.4 25.6 3.2 9.6 12.8 12.8 22.4 12.8h704c9.6 0 19.2-3.2 25.6-12.8 6.4-6.4 9.6-16 6.4-25.6-25.6-134.4-121.6-240-243.2-288z"></path></g></svg><span><a class="author-item" href="https://javaguide.cn/" target="_blank" rel="noopener noreferrer">Guide</a></span><span property="author" content="Guide"></span></span><span class="category-info" arialabel="分类🌈" isoriginal="false" pageview="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon category-icon" viewbox="0 0 1024 1024" arialabelledby="category"><title id="category" lang="en">category icon</title><g fill="currentColor"><path d="M148.41 106.992h282.176c22.263 0 40.31 18.048 40.31 40.31V429.48c0 22.263-18.047 40.31-40.31 40.31H148.41c-22.263 0-40.311-18.047-40.311-40.31V147.302c0-22.263 18.048-40.31 40.311-40.31zM147.556 553.478H429.73c22.263 0 40.311 18.048 40.311 40.31v282.176c0 22.263-18.048 40.312-40.31 40.312H147.555c-22.263 0-40.311-18.049-40.311-40.312V593.79c0-22.263 18.048-40.311 40.31-40.311zM593.927 106.992h282.176c22.263 0 40.31 18.048 40.31 40.31V429.48c0 22.263-18.047 40.31-40.31 40.31H593.927c-22.263 0-40.311-18.047-40.311-40.31V147.302c0-22.263 18.048-40.31 40.31-40.31zM730.22 920.502H623.926c-40.925 0-74.22-33.388-74.22-74.425V623.992c0-41.038 33.387-74.424 74.425-74.424h222.085c41.038 0 74.424 33.226 74.424 74.067v114.233c0 10.244-8.304 18.548-18.547 18.548s-18.548-8.304-18.548-18.548V623.635c0-20.388-16.746-36.974-37.33-36.974H624.13c-20.585 0-37.331 16.747-37.331 37.33v222.086c0 20.585 16.654 37.331 37.126 37.331H730.22c10.243 0 18.547 8.304 18.547 18.547 0 10.244-8.304 18.547-18.547 18.547z"></path></g></svg><ul class="categories-wrapper"><li class="category clickable" role="navigation">数据库</li><meta property="articleSection" content="数据库"></ul></span><span arialabel="标签🏷" isoriginal="false" pageview="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon tag-icon" viewbox="0 0 1024 1024" arialabelledby="tag"><title id="tag" lang="en">tag icon</title><g fill="currentColor"><path d="M939.902 458.563L910.17 144.567c-1.507-16.272-14.465-29.13-30.737-30.737L565.438 84.098h-.402c-3.215 0-5.726 1.005-7.634 2.913l-470.39 470.39a10.004 10.004 0 000 14.164l365.423 365.424c1.909 1.908 4.42 2.913 7.132 2.913s5.223-1.005 7.132-2.913l470.39-470.39c2.01-2.11 3.014-5.023 2.813-8.036zm-240.067-72.121c-35.458 0-64.286-28.828-64.286-64.286s28.828-64.285 64.286-64.285 64.286 28.828 64.286 64.285-28.829 64.286-64.286 64.286z"></path></g></svg><ul class="tags-wrapper"><li class="tag clickable" role="navigation">MySQL</li></ul><meta property="keywords" content="MySQL"></span><span class="date-info" arialabel="写作日期📅" isoriginal="false" pageview="false" color="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon calendar-icon" viewbox="0 0 1024 1024" arialabelledby="calendar"><title id="calendar" lang="en">calendar icon</title><g fill="currentColor"><path d="M716.4 110.137c0-18.753-14.72-33.473-33.472-33.473-18.753 0-33.473 14.72-33.473 33.473v33.473h66.993v-33.473zm-334.87 0c0-18.753-14.72-33.473-33.473-33.473s-33.52 14.72-33.52 33.473v33.473h66.993v-33.473zm468.81 33.52H716.4v100.465c0 18.753-14.72 33.473-33.472 33.473a33.145 33.145 0 01-33.473-33.473V143.657H381.53v100.465c0 18.753-14.72 33.473-33.473 33.473a33.145 33.145 0 01-33.473-33.473V143.657H180.6A134.314 134.314 0 0046.66 277.595v535.756A134.314 134.314 0 00180.6 947.289h669.74a134.36 134.36 0 00133.94-133.938V277.595a134.314 134.314 0 00-133.94-133.938zm33.473 267.877H147.126a33.145 33.145 0 01-33.473-33.473c0-18.752 14.72-33.473 33.473-33.473h736.687c18.752 0 33.472 14.72 33.472 33.473a33.145 33.145 0 01-33.472 33.473z"></path></g></svg><span>2021年11月6日</span><meta property="datePublished" content="2021-11-06T11:23:34.000Z"></span><!----><span class="words-info" arialabel="字数🔠" isoriginal="false" pageview="false" color="false"><svg xmlns="http://www.w3.org/2000/svg" class="icon word-icon" viewbox="0 0 1024 1024" arialabelledby="word"><title id="word" lang="en">word icon</title><g fill="currentColor"><path d="M518.217 432.64V73.143A73.143 73.143 0 01603.43 1.097a512 512 0 01419.474 419.474 73.143 73.143 0 01-72.046 85.212H591.36a73.143 73.143 0 01-73.143-73.143z"></path><path d="M493.714 566.857h340.297a73.143 73.143 0 0173.143 85.577A457.143 457.143 0 11371.566 117.76a73.143 73.143 0 0185.577 73.143v339.383a36.571 36.571 0 0036.571 36.571z"></path></g></svg><span>约 3774 字</span><meta property="wordCount" content="3774"></span></div><hr></div><div class="toc-place-holder"><aside id="toc-list"><div class="toc-header">此页内容</div><div class="toc-wrapper"><ul class="toc-list"><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#一致性非锁定读和锁定读" class="router-link-active router-link-exact-active toc-link level2">一致性非锁定读和锁定读</a></li><ul class="toc-list"><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#一致性非锁定读" class="router-link-active router-link-exact-active toc-link level3">一致性非锁定读</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#锁定读" class="router-link-active router-link-exact-active toc-link level3">锁定读</a></li><!----><!--]--></ul><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#innodb-对-mvcc-的实现" class="router-link-active router-link-exact-active toc-link level2">InnoDB 对 MVCC 的实现</a></li><ul class="toc-list"><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#隐藏字段" class="router-link-active router-link-exact-active toc-link level3">隐藏字段</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#readview" class="router-link-active router-link-exact-active toc-link level3">ReadView</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#undo-log" class="router-link-active router-link-exact-active toc-link level3">undo-log</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#数据可见性算法" class="router-link-active router-link-exact-active toc-link level3">数据可见性算法</a></li><!----><!--]--></ul><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#rc-和-rr-隔离级别下-mvcc-的差异" class="router-link-active router-link-exact-active toc-link level2">RC 和 RR 隔离级别下 MVCC 的差异</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#mvcc-解决不可重复读问题" class="router-link-active router-link-exact-active toc-link level2">MVCC 解决不可重复读问题</a></li><ul class="toc-list"><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#在-rc-下-readview-生成情况" class="router-link-active router-link-exact-active toc-link level3">在 RC 下 ReadView 生成情况</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#在-rr-下-readview-生成情况" class="router-link-active router-link-exact-active toc-link level3">在 RR 下 ReadView 生成情况</a></li><!----><!--]--></ul><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#mvcc➕next-key-lock-防止幻读" class="router-link-active router-link-exact-active toc-link level2">MVCC➕Next-key-Lock 防止幻读</a></li><!----><!--]--><!--[--><li class="toc-item"><a aria-current="page" href="/database/mysql/innodb-implementation-of-mvcc.html#参考" class="router-link-active router-link-exact-active toc-link level2">参考</a></li><!----><!--]--></ul></div></aside></div><!----><div class="theme-hope-content"><!--[--><h2 id="一致性非锁定读和锁定读" tabindex="-1"><a class="header-anchor" href="#一致性非锁定读和锁定读" aria-hidden="true">#</a> 一致性非锁定读和锁定读</h2><h3 id="一致性非锁定读" tabindex="-1"><a class="header-anchor" href="#一致性非锁定读" aria-hidden="true">#</a> 一致性非锁定读</h3><p>对于 <a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html" target="_blank" rel="noopener noreferrer"><strong>一致性非锁定读（Consistent Nonlocking Reads）</strong> <span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见</p><p>在 <code>InnoDB</code> 存储引擎中，<a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html" target="_blank" rel="noopener noreferrer">多版本控制 (multi versioning)<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a> 就是对非锁定读的实现。如果读取的行正在执行 <code>DELETE</code> 或 <code>UPDATE</code> 操作，这时读取操作不会去等待行上锁的释放。相反地，<code>InnoDB</code> 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)</p><p>在 <code>Repeatable Read</code> 和 <code>Read Committed</code> 两个隔离级别下，如果是执行普通的 <code>select</code> 语句（不包括 <code>select ... lock in share mode</code> ,<code>select ... for update</code>）则会使用 <code>一致性非锁定读（MVCC）</code>。并且在 <code>Repeatable Read</code> 下 <code>MVCC</code> 实现了可重复读和防止部分幻读</p><h3 id="锁定读" tabindex="-1"><a class="header-anchor" href="#锁定读" aria-hidden="true">#</a> 锁定读</h3><p>如果执行的是下列语句，就是 <a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html" target="_blank" rel="noopener noreferrer"><strong>锁定读（Locking Reads）</strong><span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><ul><li><code>select ... lock in share mode</code></li><li><code>select ... for update</code></li><li><code>insert</code>、<code>update</code>、<code>delete</code> 操作</li></ul><p>在锁定读下，读取的是数据的最新版本，这种读也被称为 <code>当前读（current read）</code>。锁定读会对读取到的记录加锁：</p><ul><li><p><code>select ... lock in share mode</code>：对记录加 <code>S</code> 锁，其它事务也可以加<code>S</code>锁，如果加 <code>x</code> 锁则会被阻塞</p></li><li><p><code>select ... for update</code>、<code>insert</code>、<code>update</code>、<code>delete</code>：对记录加 <code>X</code> 锁，且其它事务不能加任何锁</p></li></ul><p>在一致性非锁定读下，即使读取的记录已被其它事务加上 <code>X</code> 锁，这时记录也是可以被读取的，即读取的快照数据。上面说了，在 <code>Repeatable Read</code> 下 <code>MVCC</code> 防止了部分幻读，这边的 “部分” 是指在 <code>一致性非锁定读</code> 情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）。但是！如果是 <code>当前读</code> ，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。所以， <strong><code>InnoDB</code> 在实现<code>Repeatable Read</code> 时，如果执行的是当前读，则会对读取的记录使用 <code>Next-key Lock</code> ，来防止其它事务在间隙间插入数据</strong></p><h2 id="innodb-对-mvcc-的实现" tabindex="-1"><a class="header-anchor" href="#innodb-对-mvcc-的实现" aria-hidden="true">#</a> InnoDB 对 MVCC 的实现</h2><p><code>MVCC</code> 的实现依赖于：<strong>隐藏字段、Read View、undo log</strong>。在内部实现中，<code>InnoDB</code> 通过数据行的 <code>DB_TRX_ID</code> 和 <code>Read View</code> 来判断数据的可见性，如不可见，则通过数据行的 <code>DB_ROLL_PTR</code> 找到 <code>undo log</code> 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 <code>Read View</code> 之前已经提交的修改和该事务本身做的修改</p><h3 id="隐藏字段" tabindex="-1"><a class="header-anchor" href="#隐藏字段" aria-hidden="true">#</a> 隐藏字段</h3><p>在内部，<code>InnoDB</code> 存储引擎为每行数据添加了三个 <a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html" target="_blank" rel="noopener noreferrer">隐藏字段<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>：</p><ul><li><code>DB_TRX_ID（6字节）</code>：表示最后一次插入或更新该行的事务 id。此外，<code>delete</code> 操作在内部被视为更新，只不过会在记录头 <code>Record header</code> 中的 <code>deleted_flag</code> 字段将其标记为已删除</li><li><code>DB_ROLL_PTR（7字节）</code> 回滚指针，指向该行的 <code>undo log</code> 。如果该行未被更新，则为空</li><li><code>DB_ROW_ID（6字节）</code>：如果没有设置主键且该表没有唯一非空索引时，<code>InnoDB</code> 会使用该 id 来生成聚簇索引</li></ul><h3 id="readview" tabindex="-1"><a class="header-anchor" href="#readview" aria-hidden="true">#</a> ReadView</h3><div class="language-c ext-c line-numbers-mode"><pre class="language-c"><code>class ReadView <span class="token punctuation">{</span>
  <span class="token comment">/* ... */</span>
private<span class="token operator">:</span>
  <span class="token class-name">trx_id_t</span> m_low_limit_id<span class="token punctuation">;</span>      <span class="token comment">/* 大于等于这个 ID 的事务均不可见 */</span>

  <span class="token class-name">trx_id_t</span> m_up_limit_id<span class="token punctuation">;</span>       <span class="token comment">/* 小于这个 ID 的事务均可见 */</span>

  <span class="token class-name">trx_id_t</span> m_creator_trx_id<span class="token punctuation">;</span>    <span class="token comment">/* 创建该 Read View 的事务ID */</span>

  <span class="token class-name">trx_id_t</span> m_low_limit_no<span class="token punctuation">;</span>      <span class="token comment">/* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */</span>

  <span class="token class-name">ids_t</span> m_ids<span class="token punctuation">;</span>                  <span class="token comment">/* 创建 Read View 时的活跃事务列表 */</span>

  m_closed<span class="token punctuation">;</span>                     <span class="token comment">/* 标记 Read View 是否 close */</span>
<span class="token punctuation">}</span>
</code></pre><div class="line-numbers" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><p><a href="https://github.com/facebook/mysql-8.0/blob/8.0/storage/innobase/include/read0types.h#L298" target="_blank" rel="noopener noreferrer"><code>Read View</code><span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a> 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”</p><p>主要有以下字段：</p><ul><li><code>m_low_limit_id</code>：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见</li><li><code>m_up_limit_id</code>：活跃事务列表 <code>m_ids</code> 中最小的事务 ID，如果 <code>m_ids</code> 为空，则 <code>m_up_limit_id</code> 为 <code>m_low_limit_id</code>。小于这个 ID 的数据版本均可见</li><li><code>m_ids</code>：<code>Read View</code> 创建时其他未提交的活跃事务 ID 列表。创建 <code>Read View</code>时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。<code>m_ids</code> 不包括当前事务自己和已提交的事务（正在内存中）</li><li><code>m_creator_trx_id</code>：创建该 <code>Read View</code> 的事务 ID</li></ul><p><strong>事务可见性示意图</strong>（<a href="https://leviathan.vip/2019/03/20/InnoDB%E7%9A%84%E4%BA%8B%E5%8A%A1%E5%88%86%E6%9E%90-MVCC/#MVCC-1" target="_blank" rel="noopener noreferrer">图源<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>）：</p><p><img src="https://leviathan.vip/2019/03/20/InnoDB的事务分析-MVCC/trans_visible.jpg" alt="trans_visible" loading="lazy"></p><h3 id="undo-log" tabindex="-1"><a class="header-anchor" href="#undo-log" aria-hidden="true">#</a> undo-log</h3><p><code>undo log</code> 主要有两个作用：</p><ul><li>当事务回滚时用于将数据恢复到修改前的样子</li><li>另一个作用是 <code>MVCC</code> ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 <code>undo log</code> 读取之前的版本数据，以此实现非锁定读</li></ul><p><strong>在 <code>InnoDB</code> 存储引擎中 <code>undo log</code> 分为两种： <code>insert undo log</code> 和 <code>update undo log</code>：</strong></p><ol><li><strong><code>insert undo log</code></strong> ：指在 <code>insert</code> 操作中产生的 <code>undo log</code>。因为 <code>insert</code> 操作的记录只对事务本身可见，对其他事务不可见，故该 <code>undo log</code> 可以在事务提交后直接删除。不需要进行 <code>purge</code> 操作</li></ol><p><strong><code>insert</code> 时的数据初始状态：</strong></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/317e91e1-1ee1-42ad-9412-9098d5c6a9ad.png" alt="" loading="lazy"></p><ol start="2"><li><strong><code>update undo log</code></strong> ：<code>update</code> 或 <code>delete</code> 操作中产生的 <code>undo log</code>。该 <code>undo log</code>可能需要提供 <code>MVCC</code> 机制，因此不能在事务提交时就进行删除。提交时放入 <code>undo log</code> 链表，等待 <code>purge线程</code> 进行最后的删除</li></ol><p><strong>数据第一次被修改时：</strong></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/c52ff79f-10e6-46cb-b5d4-3c9cbcc1934a.png" alt="" loading="lazy"></p><p><strong>数据第二次被修改时：</strong></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/6a276e7a-b0da-4c7b-bdf7-c0c7b7b3b31c.png" alt="" loading="lazy"></p><p>不同事务或者相同事务的对同一记录行的修改，会使该记录行的 <code>undo log</code> 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。</p><h3 id="数据可见性算法" tabindex="-1"><a class="header-anchor" href="#数据可见性算法" aria-hidden="true">#</a> 数据可见性算法</h3><p>在 <code>InnoDB</code> 存储引擎中，创建一个新事务后，执行每个 <code>select</code> 语句前，都会创建一个快照（Read View），<strong>快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号</strong>。其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，<code>InnoDB</code> 会将该记录行的 <code>DB_TRX_ID</code> 与 <code>Read View</code> 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件</p><p><a href="https://github.com/facebook/mysql-8.0/blob/8.0/storage/innobase/include/read0types.h#L161" target="_blank" rel="noopener noreferrer">具体的比较算法<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>如下：<a href="https://leviathan.vip/2019/03/20/InnoDB%E7%9A%84%E4%BA%8B%E5%8A%A1%E5%88%86%E6%9E%90-MVCC/#MVCC-1" target="_blank" rel="noopener noreferrer">图源<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/8778836b-34a8-480b-b8c7-654fe207a8c2.png" alt="" loading="lazy"></p><ol><li><p>如果记录 DB_TRX_ID &lt; m_up_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了，所以该记录行的值对当前事务是可见的</p></li><li><p>如果 DB_TRX_ID &gt;= m_low_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤 5</p></li><li><p>m_ids 为空，则表明在当前事务创建快照之前，修改该行的事务就已经提交了，所以该记录行的值对当前事务是可见的</p></li><li><p>如果 m_up_limit_id &lt;= DB_TRX_ID &lt; m_low_limit_id，表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表 m_ids 进行查找（源码中是用的二分查找，因为是有序的）</p><ul><li><p>如果在活跃事务列表 m_ids 中能找到 DB_TRX_ID，表明：① 在当前事务创建快照前，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了，但没有提交；或者 ② 在当前事务创建快照后，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了。这些情况下，这个记录行的值对当前事务都是不可见的。跳到步骤 5</p></li><li><p>在活跃事务列表中找不到，则表明“id 为 trx_id 的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见</p></li></ul></li><li><p>在该记录行的 DB_ROLL_PTR 指针所指向的 <code>undo log</code> 取出快照记录，用快照记录的 DB_TRX_ID 跳到步骤 1 重新开始判断，直到找到满足的快照版本或返回空</p></li></ol><h2 id="rc-和-rr-隔离级别下-mvcc-的差异" tabindex="-1"><a class="header-anchor" href="#rc-和-rr-隔离级别下-mvcc-的差异" aria-hidden="true">#</a> RC 和 RR 隔离级别下 MVCC 的差异</h2><p>在事务隔离级别 <code>RC</code> 和 <code>RR</code> （InnoDB 存储引擎的默认事务隔离级别）下，<code>InnoDB</code> 存储引擎使用 <code>MVCC</code>（非锁定一致性读），但它们生成 <code>Read View</code> 的时机却不同</p><ul><li>在 RC 隔离级别下的 <strong><code>每次select</code></strong> 查询前都生成一个<code>Read View</code> (m_ids 列表)</li><li>在 RR 隔离级别下只在事务开始后 <strong><code>第一次select</code></strong> 数据前生成一个<code>Read View</code>（m_ids 列表）</li></ul><h2 id="mvcc-解决不可重复读问题" tabindex="-1"><a class="header-anchor" href="#mvcc-解决不可重复读问题" aria-hidden="true">#</a> MVCC 解决不可重复读问题</h2><p>虽然 RC 和 RR 都通过 <code>MVCC</code> 来读取快照数据，但由于 <strong>生成 Read View 时机不同</strong>，从而在 RR 级别下实现可重复读</p><p>举个例子：</p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/6fb2b9a1-5f14-4dec-a797-e4cf388ed413.png" alt="" loading="lazy"></p><h3 id="在-rc-下-readview-生成情况" tabindex="-1"><a class="header-anchor" href="#在-rc-下-readview-生成情况" aria-hidden="true">#</a> 在 RC 下 ReadView 生成情况</h3><ol><li><p><strong><code>假设时间线来到 T4 ，那么此时数据行 id = 1 的版本链为</code>：</strong></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/a3fd1ec6-8f37-42fa-b090-7446d488fd04.png" alt="" loading="lazy"></p></li></ol><p>由于 RC 级别下每次查询都会生成<code>Read View</code> ，并且事务 101、102 并未提交，此时 <code>103</code> 事务生成的 <code>Read View</code> 中活跃的事务 <strong><code>m_ids</code> 为：[101,102]</strong> ，<code>m_low_limit_id</code>为：104，<code>m_up_limit_id</code>为：101，<code>m_creator_trx_id</code> 为：103</p><ul><li>此时最新记录的 <code>DB_TRX_ID</code> 为 101，m_up_limit_id &lt;= 101 &lt; m_low_limit_id，所以要在 <code>m_ids</code> 列表中查找，发现 <code>DB_TRX_ID</code> 存在列表中，那么这个记录不可见</li><li>根据 <code>DB_ROLL_PTR</code> 找到 <code>undo log</code> 中的上一版本记录，上一条记录的 <code>DB_TRX_ID</code> 还是 101，不可见</li><li>继续找上一条 <code>DB_TRX_ID</code>为 1，满足 1 &lt; m_up_limit_id，可见，所以事务 103 查询到数据为 <code>name = 菜花</code></li></ul><ol start="2"><li><p><strong><code>时间线来到 T6 ，数据的版本链为</code>：</strong></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/528559e9-dae8-4d14-b78d-a5b657c88391.png" alt="markdown" loading="lazy"></p></li></ol><p>因为在 RC 级别下，重新生成 <code>Read View</code>，这时事务 101 已经提交，102 并未提交，所以此时 <code>Read View</code> 中活跃的事务 <strong><code>m_ids</code>：[102]</strong> ，<code>m_low_limit_id</code>为：104，<code>m_up_limit_id</code>为：102，<code>m_creator_trx_id</code>为：103</p><ul><li><p>此时最新记录的 <code>DB_TRX_ID</code> 为 102，m_up_limit_id &lt;= 102 &lt; m_low_limit_id，所以要在 <code>m_ids</code> 列表中查找，发现 <code>DB_TRX_ID</code> 存在列表中，那么这个记录不可见</p></li><li><p>根据 <code>DB_ROLL_PTR</code> 找到 <code>undo log</code> 中的上一版本记录，上一条记录的 <code>DB_TRX_ID</code> 为 101，满足 101 &lt; m_up_limit_id，记录可见，所以在 <code>T6</code> 时间点查询到数据为 <code>name = 李四</code>，与时间 T4 查询到的结果不一致，不可重复读！</p></li></ul><ol start="3"><li><strong><code>时间线来到 T9 ，数据的版本链为</code>：</strong></li></ol><p><img src="https://ddmcc-1255635056.file.myqcloud.com/6f82703c-36a1-4458-90fe-d7f4edbac71a.png" alt="markdown" loading="lazy"></p><p>重新生成 <code>Read View</code>， 这时事务 101 和 102 都已经提交，所以 <strong>m_ids</strong> 为空，则 m_up_limit_id = m_low_limit_id = 104，最新版本事务 ID 为 102，满足 102 &lt; m_low_limit_id，可见，查询结果为 <code>name = 赵六</code></p><blockquote><p><strong>总结：</strong> <strong>在 RC 隔离级别下，事务在每次查询开始时都会生成并设置新的 Read View，所以导致不可重复读</strong></p></blockquote><h3 id="在-rr-下-readview-生成情况" tabindex="-1"><a class="header-anchor" href="#在-rr-下-readview-生成情况" aria-hidden="true">#</a> 在 RR 下 ReadView 生成情况</h3><p><strong>在可重复读级别下，只会在事务开始后第一次读取数据时生成一个 Read View（m_ids 列表）</strong></p><ol><li><strong><code>在 T4 情况下的版本链为</code>：</strong></li></ol><p><img src="https://ddmcc-1255635056.file.myqcloud.com/0e906b95-c916-4f30-beda-9cb3e49746bf.png" alt="markdown" loading="lazy"></p><p>在当前执行 <code>select</code> 语句时生成一个 <code>Read View</code>，此时 <strong><code>m_ids</code>：[101,102]</strong> ，<code>m_low_limit_id</code>为：104，<code>m_up_limit_id</code>为：101，<code>m_creator_trx_id</code> 为：103</p><p>此时和 RC 级别下一样：</p><ul><li>最新记录的 <code>DB_TRX_ID</code> 为 101，m_up_limit_id &lt;= 101 &lt; m_low_limit_id，所以要在 <code>m_ids</code> 列表中查找，发现 <code>DB_TRX_ID</code> 存在列表中，那么这个记录不可见</li><li>根据 <code>DB_ROLL_PTR</code> 找到 <code>undo log</code> 中的上一版本记录，上一条记录的 <code>DB_TRX_ID</code> 还是 101，不可见</li><li>继续找上一条 <code>DB_TRX_ID</code>为 1，满足 1 &lt; m_up_limit_id，可见，所以事务 103 查询到数据为 <code>name = 菜花</code></li></ul><ol start="2"><li><p><strong><code>时间点 T6 情况下</code>：</strong></p><p><img src="https://ddmcc-1255635056.file.myqcloud.com/79ed6142-7664-4e0b-9023-cf546586aa39.png" alt="markdown" loading="lazy"></p><p>在 RR 级别下只会生成一次<code>Read View</code>，所以此时依然沿用 <strong><code>m_ids</code> ：[101,102]</strong> ，<code>m_low_limit_id</code>为：104，<code>m_up_limit_id</code>为：101，<code>m_creator_trx_id</code> 为：103</p></li></ol><ul><li><p>最新记录的 <code>DB_TRX_ID</code> 为 102，m_up_limit_id &lt;= 102 &lt; m_low_limit_id，所以要在 <code>m_ids</code> 列表中查找，发现 <code>DB_TRX_ID</code> 存在列表中，那么这个记录不可见</p></li><li><p>根据 <code>DB_ROLL_PTR</code> 找到 <code>undo log</code> 中的上一版本记录，上一条记录的 <code>DB_TRX_ID</code> 为 101，不可见</p></li><li><p>继续根据 <code>DB_ROLL_PTR</code> 找到 <code>undo log</code> 中的上一版本记录，上一条记录的 <code>DB_TRX_ID</code> 还是 101，不可见</p></li><li><p>继续找上一条 <code>DB_TRX_ID</code>为 1，满足 1 &lt; m_up_limit_id，可见，所以事务 103 查询到数据为 <code>name = 菜花</code></p></li></ul><ol start="3"><li><strong>时间点 T9 情况下：</strong></li></ol><p><img src="https://ddmcc-1255635056.file.myqcloud.com/cbbedbc5-0e3c-4711-aafd-7f3d68a4ed4e.png" alt="markdown" loading="lazy"></p><p>此时情况跟 T6 完全一样，由于已经生成了 <code>Read View</code>，此时依然沿用 <strong><code>m_ids</code> ：[101,102]</strong> ，所以查询结果依然是 <code>name = 菜花</code></p><h2 id="mvcc➕next-key-lock-防止幻读" tabindex="-1"><a class="header-anchor" href="#mvcc➕next-key-lock-防止幻读" aria-hidden="true">#</a> MVCC➕Next-key-Lock 防止幻读</h2><p><code>InnoDB</code>存储引擎在 RR 级别下通过 <code>MVCC</code>和 <code>Next-key Lock</code> 来解决幻读问题：</p><p><strong>1、执行普通 <code>select</code>，此时会以 <code>MVCC</code> 快照读的方式读取数据</strong></p><p>在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 <code>Read View</code> ，并使用至事务提交。所以在生成 <code>Read View</code> 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”</p><p><strong>2、执行 select...for update/lock in share mode、insert、update、delete 等当前读</strong></p><p>在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！<code>InnoDB</code> 使用 <a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks" target="_blank" rel="noopener noreferrer">Next-key Lock<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a> 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读</p><h2 id="参考" tabindex="-1"><a class="header-anchor" href="#参考" aria-hidden="true">#</a> 参考</h2><ul><li><strong>《MySQL 技术内幕 InnoDB 存储引擎第 2 版》</strong></li><li><a href="https://tech.meituan.com/2014/08/20/innodb-lock.html" target="_blank" rel="noopener noreferrer">Innodb 中的事务隔离级别和锁的关系<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></li><li><a href="https://blog.csdn.net/qq_35190492/article/details/109044141" target="_blank" rel="noopener noreferrer">MySQL 事务与 MVCC 如何实现的隔离级别<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></li><li><a href="https://leviathan.vip/2019/03/20/InnoDB%E7%9A%84%E4%BA%8B%E5%8A%A1%E5%88%86%E6%9E%90-MVCC/" target="_blank" rel="noopener noreferrer">InnoDB 事务分析-MVCC<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></li></ul><!--]--></div><!----><footer class="page-meta"><div class="meta-item edit-link"><a href="https://github.com/Snailclimb/JavaGuide/edit/main/docs/database/mysql/innodb-implementation-of-mvcc.md" rel="noopener noreferrer" target="_blank" arialabel="编辑此页" class="nav-link label"><!--[--><svg xmlns="http://www.w3.org/2000/svg" class="icon edit-icon" viewbox="0 0 1024 1024" arialabelledby="edit"><title id="edit" lang="en">edit icon</title><g fill="currentColor"><path d="M430.818 653.65a60.46 60.46 0 0 1-50.96-93.281l71.69-114.012 7.773-10.365L816.038 80.138A60.46 60.46 0 0 1 859.225 62a60.46 60.46 0 0 1 43.186 18.138l43.186 43.186a60.46 60.46 0 0 1 0 86.373L588.879 565.55l-8.637 8.637-117.466 68.234a60.46 60.46 0 0 1-31.958 11.229z"></path><path d="M728.802 962H252.891A190.883 190.883 0 0 1 62.008 771.98V296.934a190.883 190.883 0 0 1 190.883-192.61h267.754a60.46 60.46 0 0 1 0 120.92H252.891a69.962 69.962 0 0 0-69.098 69.099V771.98a69.962 69.962 0 0 0 69.098 69.098h475.911A69.962 69.962 0 0 0 797.9 771.98V503.363a60.46 60.46 0 1 1 120.922 0V771.98A190.883 190.883 0 0 1 728.802 962z"></path></g></svg><!--]-->编辑此页<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!----></a></div><div class="meta-item update-time"><span class="label">上次编辑于: </span><span class="info">2021/11/9 18:47:58</span></div><div class="meta-item contributors"><span class="label">贡献者: </span><!--[--><!--[--><span class="contributor" title="email: koushuangbwcx@163.com">guide</span><!--]--><!--]--></div></footer><nav class="page-nav"><a href="/database/mysql/transaction-isolation-level.html" class="nav-link prev" arialabel="MySQL事务隔离级别详解"><div class="hint"><span class="arrow left"></span>上一页</div><div class="link"><!---->MySQL事务隔离级别详解</div></a><a href="/database/mysql/how-sql-executed-in-mysql.html" class="nav-link next" arialabel="SQL语句在MySQL中的执行过程"><div class="hint">下一页<span class="arrow right"></span></div><div class="link">SQL语句在MySQL中的执行过程<!----></div></a></nav><!----><!----></main><!--]--><footer class="footer-wrapper"><div class="footer"><a href="https://beian.miit.gov.cn/" target="_blank">鄂ICP备2020015769号-1</a></div><div class="copyright">Copyright © 2022 Guide</div></footer></div><!--]--><!----><!--]--></div>
    <script type="module" src="/assets/app.93341f6d.js" defer></script>
  </body>
</html>
